﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LPY.LeHealth.Model;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using LPY.LeHealth.DTO;



namespace LPY.LeHealth.DAL
{
    using ServiceStack.OrmLite;
    using ServiceStack.OrmLite.SqlServer;
    public class My_questions_DAL : GenericBaseDAL<questions>
    {
        //string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
        /// <summary>
        /// 根据状态显示我的问答显示
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<questions> QuestionShow(int id, string state)
        {
            using (var cnn = DbConnection)
            {

                //            var sql = cnn.From<questions>()
                //.Join<questions, crowd>((q, c) => q.Id == c.Qid, SqlServerTableHint.ReadUncommitted).Where(m => m.Uid == id && m.Qstate == state);
                var rtv = cnn.Select<questions>().Where(m => m.Uid == id && m.Qstate == state).ToList();
                return rtv;
            }
        }
        /// <summary>
        /// 双表联查练习
        /// </summary>
        /// <returns></returns>
        //public List<Combo> shows()
        //{
        //    using (var cnn = DbConnection)
        //    {

        //        var sql = cnn.From<Combo>().LeftJoin<Hospital>((c, h) => c.HospitalId == h.Id);
        //        var rtv = cnn.Select<Combo>();
        //        return rtv;
        //    }
        //}
        /// <summary>
         //根据体检分类id和体检项id显示前5条
         //</summary>
         //<param name="id"></param>
         //<param name="eid"></param>
         //<returns></returns>
        public List<questions> GetQuestionshow(int eid, int id)
        {
            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m => m.Tid == eid && m.ttid == id).Take(5).ToList();
                return rtv;
                //.OrderByDescending(m=>m.Qdate.ToString("yyyy/MM/dd"))
            }
        }
        /// <summary>
        //根据体检分类id和体检项id显示全部
        //</summary>
        //<param name="id"></param>
        //<param name="eid"></param>
        //<returns></returns>
        public List<questions> GetQuestionshowall(int eid, int id)
        {
            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m => m.Tid == eid && m.ttid == id).ToList();
                return rtv;
                //.OrderByDescending(m=>m.Qdate.ToString("yyyy/MM/dd"))
            }
        }

        /// <summary>
        //根据体检分类id显示问答信息前5条
        //</summary>
        //<param name="id"></param>
        //<param name="eid"></param>
        //<returns></returns>
        public List<questions> GetQuestionId(int Id)
        {
            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m => m.Tid == Id).Take(2).ToList();
                return rtv;
                //.OrderByDescending(m=>m.Qdate.ToString("yyyy/MM/dd"))
            }
        }
        /// <summary>
        /// 根据Id查询数据表数量
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public int GetCount(int Id)
        { 
              using(var cnn=DbConnection)
              {
                  var rtv = cnn.Select<questions>().Where(m => m.Tid == Id).Count();
                  return rtv;
              }
        }
        /// <summary>
        //根据体检分类id显示问答信息
        //</summary>
        //<param name="id"></param>
        //<param name="eid"></param>
        //<returns></returns>
        public List<questions> GetQuestionIdall(int Id)
        {

            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m => m.Tid == Id).ToList();
                return rtv;
                //.OrderByDescending(m=>m.Qdate.ToString("yyyy/MM/dd"))
            }
        }
        /// <summary>
        /// 显示问题表的所有信息
        /// </summary>
        /// <returns></returns>
        public List<questions> GetQueShow()
        {
            using (var cnn=DbConnection)
            {
                var rtv = cnn.Select<questions>();
                return rtv;
            }
        }
        /// <summary>
        /// 查询默认显示的用户提问的问题前几条
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="Qstate"></param>
        /// <returns></returns>
        public List<questions> GetNoConment(int uid, string Qstate)
        {
            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m => m.Uid==uid&&m.Qstate==Qstate).Take(5).ToList();
                return rtv;
            }
        }
        /// <summary>
        /// 查询默认显示的用户提问的问题
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="Qstate"></param>
        /// <returns></returns>
        public List<questions> GetNoConmentall(int uid, string Qstate)
        {
            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m => m.Uid == uid && m.Qstate == Qstate).ToList();
                return rtv;
            }
        }

        /// <summary>
        /// 根据ID显示提问问题
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="Qstate"></param>
        /// <returns></returns>
        public List<questions> GetIdShow(int id)
        {
            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m => m.Id == id).ToList();
                return rtv;
            }
        }


        /// <summary>
        /// 搜索
        /// </summary>
        /// <param name="Tid"></param>
        /// <param name="content"></param>
        /// <returns></returns>
        public List<questions> GetQuesionSeach(string content)
        {
            using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().Where(m =>m.content.Contains(content)).ToList();
                return rtv;
            }
        }
        /// <summary>
        /// 获取解决问题数量
        /// </summary>
        /// <returns></returns>
        public int GetQuesionCount()
        {
             using (var cnn = DbConnection)
            {
                var rtv = cnn.Select<questions>().ToList();

                return rtv.Count();
            }
        }


    }
}
